# _*_ coding:utf-8 _*_
# @Time  : 2022.07.27
# @Author: zizlee
import shutil
import json
import pathlib
from collections import Counter
from fastapi import APIRouter, Header, Body, Query, UploadFile, Form
from pydantic import BaseModel, ValidationError, validator, datetime_parse
from hutool import security
from db import FAConnection
from v1_all_api import constant
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import file_utils, datetime_utils, char_utils
from configs import  FILE_STORAGE

from . import work_utils

work_lecture_api = APIRouter()


class LectureItem(BaseModel):
    create_date: datetime_parse.date
    title: str
    applicant: str
    lecture_type: str
    lecture_scale: str
    swiss_coin: float
    note: str
    exchange: str
    is_active: int = 1

    @validator('lecture_type')
    def validate_lecture_type(cls, value):
        if value not in constant.LECTURE_TYPES.keys():
            raise ValidationError('lecture type invalid!')
        return value

    @validator('lecture_scale')
    def validate_lecture_scale(cls, value):
        if value not in constant.LECTURE_SCALE_TYPES.keys():
            raise ValidationError('lecture scale type invalid!')
        return value

    @validator('exchange')
    def validator_exchange(cls, value):
        if value not in ['CFE', 'SHF', 'CZC', 'DCE', 'GFE', 'NO']:
            raise ValueError('不合法的交易所选项')
        return value


# 删除路演记录（管理员）
@work_lecture_api.delete('/manage/')
def delete_lecture_record(ruizy_token: str = Header(...), rid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    # 删除记录
    with FAConnection() as cursor:
        # 查询删除附件
        cursor.execute('SELECT id,annex_url,prse_lecture_id FROM work_lecture WHERE id=%s LIMIT 1;', [rid])
        obj = cursor.fetchone()
        if not obj:
            return AllResponse.validate_error(msg='删除的记录不存在!')
        annex_file = pathlib.Path(FILE_STORAGE).joinpath(obj['annex_url'])
        c = cursor.execute('DELETE FROM work_lecture WHERE id=%s LIMIT 1;', [rid])
        # prse_lecture_id==0的时候才能删除附件，否则协同服务里的附件会被删除
        if c > 0 and obj['prse_lecture_id'] == 0 and annex_file.exists() and annex_file.is_file():
            annex_file.unlink()
    return AllResponse.operate_successfully()


# 分片将附件上传
@work_lecture_api.post('/sliceAnnexFile/')
async def slice_annex_file(
        ruizy_token: str = Header(...),
        chunk_index: int = Form(...),
        chunk_count: int = Form(...),
        chunk_data: UploadFile = Form(...),
        file_name: str = Form(...)
):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    cache_folder = char_utils.hash_string(src_string=file_name,
                                          salt={'uid': str(person['uid']), 'work_scope': 'Lecture'})
    abs_cache_folder = pathlib.Path(f'{FILE_STORAGE}').joinpath(f'Temp/Works').joinpath(cache_folder)
    if not abs_cache_folder.exists():
        abs_cache_folder.mkdir(parents=True)

    cache_file = f'{chunk_index}_{chunk_count}_work_lecture.tmp'
    with open(abs_cache_folder.joinpath(cache_file), 'wb') as f:
        f.write(await chunk_data.read())
    return AllResponse.operate_successfully()


class FileMergeParam(BaseModel):
    file_name: str
    row_id: int


@work_lecture_api.post('/mergeAnnexFile/')
def merge_annex_file(ruizy_token: str = Header(...), merge: FileMergeParam = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    cache_folder = char_utils.hash_string(src_string=merge.file_name,
                                          salt={'uid': str(person['uid']), 'work_scope': 'Lecture'})
    abs_cache_folder = pathlib.Path(f'{FILE_STORAGE}').joinpath(f'Temp/Works').joinpath(cache_folder)
    if not abs_cache_folder.exists():
        return AllResponse.no_do_anything(msg='合并失败，文件不存在')

    # 保存文件
    save_path, sql_path = file_utils.get_file_paths(module_name='Lecture', filename=merge.file_name, hashed=True)
    with open(save_path, 'ab+') as fp:
        for slice_file in sorted(abs_cache_folder.glob('*.tmp')):
            with open(slice_file, 'rb') as fs:
                fp.write(fs.read())

    # 写入SQL
    # 名称转为半角
    title = char_utils.strQ2B(merge.file_name)
    title = file_utils.remove_filename_number_suffix(title, number_max=10)  # 去除数据编号和后缀
    # 更新数据库记录
    update_sql = "UPDATE work_lecture SET annex=%s,annex_url=%s,is_active=1 WHERE id=%s AND user_id=%s;"
    with FAConnection() as cursor:
        c = cursor.execute(update_sql, [title, sql_path, merge.row_id, person['uid']])
    msg = '合并文件: {}成功!'.format(merge.file_name)
    res = True
    if c < 1:
        # 移除文件
        tmp_file = pathlib.Path(save_path)
        if tmp_file.exists() and tmp_file.is_file():
            tmp_file.unlink()
        msg = '合并完成，但没有添加文件.'
        res = False
    else:
        # 删除缓存的文件
        shutil.rmtree(abs_cache_folder, ignore_errors=True)
    return AllResponse.operate_successfully(data={'merge': res, 'msg': msg}, msg='保存路演记录成功!')


@work_lecture_api.post('/my/')  # 添加一个路演记录
async def create_lecture(ruizy_token: str = Header(...), annex_file: UploadFile = Form(None),
                         body_data: str = Form(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    try:
        lecture = LectureItem(**json.loads(body_data))
    except json.decoder.JSONDecodeError:
        return AllResponse.validate_error(msg='parse body data Error,it must be json string')
    except ValidationError as e:
        return AllResponse.validate_error(msg='参数错误{}'.format(e))

    create_date = lecture.create_date.strftime('%Y%m%d')
    user_id = person['uid']
    # 改为审核处理
    # levels = {'S02': 'A', 'S03': 'B', 'S01': 'C', 'AA': 'A', 'AB': 'B', 'BB': 'C', 'AC': 'D', 'BC': 'E', 'CC': 'F'}
    # _level = [lecture.effect_flag, levels.get(lecture.lecture_scale)]
    # _level.sort()
    # level_flag = levels.get(''.join(_level))  # 规模对应评ABC级+配合效果ABC => ABCDE五个等级
    annex = ''
    annex_url = ''
    save_path = None
    if annex_file:  # 有附件
        save_path, sql_path = file_utils.get_file_paths(module_name='Lecture', filename=annex_file.filename,
                                                        hashed=True)
        # 保存附件到指定文件夹
        file_content = await annex_file.read()
        with open(save_path, 'wb') as fp:
            fp.write(file_content)
        await annex_file.close()
        annex = annex_file.filename
        annex_url = sql_path

    sql = """
        INSERT INTO work_lecture (user_id,create_date,title,applicant,lecture_type,lecture_scale,exchange,swiss_coin,
        effect_flag,level_flag,annex,annex_url,note,is_active) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
    """
    prm = (user_id, create_date, lecture.title, lecture.applicant, lecture.lecture_type, lecture.lecture_scale,
           lecture.exchange, lecture.swiss_coin, '', '', annex, annex_url, lecture.note, lecture.is_active)
    db_conn = FAConnection()
    success, insert_id = db_conn.insert(sql, param=prm)
    if not success and save_path:
        annex = pathlib.Path(save_path)
        if annex.is_file():
            annex.unlink()
    return AllResponse.create_successfully(data={'record_id': insert_id}) if success else AllResponse.server_error()


@work_lecture_api.get('/level/')  # 查询路演记录供评级
def query_lecture_for_level(ruizy_token: str = Header(...), level: int = Query(...),
                            page: int = Query(...), page_size: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if level == 0:  # 全部
        sql = """
            SELECT id,create_date,title,applicant,lecture_type,lecture_scale,effect_flag,effect_explain,
            level_flag,annex,annex_url 
            FROM work_lecture WHERE is_active=1 
            ORDER BY create_date DESC;
        """
        prm = []
    else:
        level = str(level)
        sql = """
            SELECT id,create_date,title,applicant,lecture_type,lecture_scale,exchange,effect_flag,effect_explain,
            level_flag,annex,annex_url 
            FROM work_lecture WHERE is_active=1 AND IF('-1'=%s,effect_flag='',effect_flag<>'') 
            ORDER BY create_date DESC;
        """
        prm = [level]
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=prm, page=page, page_size=page_size)
    for item in ret['data']:
        item['lecture_type_zh'] = constant.LECTURE_TYPES.get(item['lecture_type'])
        item['lecture_scale_zh'] = constant.LECTURE_SCALE_TYPES.get(item['lecture_scale'])
        item['exchange_name'] = constant.EXCHANGE_LIB_NAME.get(item['exchange'], '')
        item['is_exchange'] = '是' if item['exchange'] != 'NO' else '否'
    return AllResponse.operate_successfully(data=ret)


class LectureLevelItem(BaseModel):
    lecture_id: int
    effect_flag: str
    lecture_scale: str
    effect_explain: str

    @validator('effect_flag')
    def validate_effect_flag(cls, value):
        if value not in ['A', 'B', 'C']:
            raise ValidationError('effect flag invalid!')
        return value


@work_lecture_api.put('/level/')  # 路演记录评级保存
def save_lecture_effect_level(ruizy_token: str = Header(...), level: LectureLevelItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    db_conn = FAConnection()
    # 查询操作用户的名称
    op_sql = "SELECT admin_name FROM ruizy_user WHERE id=%s LIMIT 1;"
    op_user = db_conn.query(op_sql, param=[person['uid']], fetchone=True, keep_conn=True)[0]
    if not op_user:
        db_conn.close()
        return AllResponse.validate_error(msg='user not exist!')
    admin_name = op_user['admin_name']
    # 处理等级
    levels = {'S02': 'A', 'S03': 'B', 'S01': 'C', 'AA': 'A', 'AB': 'B', 'BB': 'C', 'AC': 'D', 'BC': 'E', 'CC': 'F'}
    _level = [level.effect_flag, levels.get(level.lecture_scale)]
    _level.sort()
    level_flag = levels.get(''.join(_level))  # 规模对应评ABC级+配合效果ABC => ABCDEF6个等级
    # 更新数据
    up_sql = """
        UPDATE work_lecture SET effect_flag=%s,effect_explain=%s,effect_examiner=%s,level_flag=%s WHERE id=%s LIMIT 1;
    """
    _, success = db_conn.execute(up_sql, param=[level.effect_flag, level.effect_explain, admin_name,
                                                level_flag, level.lecture_id])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@work_lecture_api.get('/my/')  # 查询我的路演记录
def query_lecture(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='params start and end invalid.')
    sql = """
        SELECT id,create_date,title,applicant,lecture_type,lecture_scale,exchange,effect_flag,level_flag,annex,annex_url 
        FROM work_lecture WHERE is_active=1 AND user_id=%s AND create_date>=%s AND create_date<=%s 
        ORDER BY create_date DESC;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[person['uid'], start_date, end_date])
    for item in records:
        item['lecture_type_zh'] = constant.LECTURE_TYPES.get(item['lecture_type'])
        item['lecture_scale_zh'] = constant.LECTURE_SCALE_TYPES.get(item['lecture_scale'])
        item['exchange_name'] = constant.EXCHANGE_LIB_NAME.get(item['exchange'], '')
        item['is_exchange'] = '是' if item['exchange'] != 'NO' else '否'
    return AllResponse.operate_successfully(data=records)


@work_lecture_api.get('/statistics/')  # 统计路演记录(管理员要所有、组长显示组员)
def query_lecture_statistics(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                             st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    # 查询日期范围中的数据
    sql = """
        SELECT a.id,a.user_id,a.create_date,a.title,a.applicant,a.lecture_type,a.lecture_scale,a.exchange,a.effect_flag,
            a.annex,a.annex_url,a.level_flag,a.swiss_coin,a.prse_lecture_id,b.admin_name  
        FROM work_lecture As a INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.is_active=1 AND a.create_date>=%s AND a.create_date<=%s 
        ORDER BY a.create_date DESC;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[start_date, end_date])

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))

    # 数据统计(数量跟评级)
    scales = {'S02': 'A', 'S03': 'B', 'S01': 'C'}
    user_dict = {}
    for item in records:
        item['lecture_scale_level'] = scales.get(item['lecture_scale'])
        item['exchange_name'] = constant.EXCHANGE_LIB_NAME.get(item['exchange'], '')
        if item['user_id'] in user_dict.keys():
            data_obj = user_dict[item['user_id']]
            data_obj['total_count'] += 1
            data_obj['total_coin'] += item['swiss_coin']
            data_obj['levels'].append(item['level_flag'])
        else:
            user_dict[item['user_id']] = {
                'user_id': item['user_id'],
                'admin_name': item['admin_name'],
                'total_count': 1,
                'total_coin': item['swiss_coin'],
                'levels': [item['level_flag']]
            }
    statistics_data = []
    total_levels = []
    total_count, total_swiss_coin = 0, 0
    for _, st in user_dict.items():
        st['levels'].sort()
        total_levels.extend(st['levels'])
        level_count = Counter(st['levels']).items()
        st['level_count'] = ''.join(['%d%s' % (v, k) for k, v in level_count if k])
        del st['levels']
        total_count += st['total_count']
        total_swiss_coin += st['total_coin']
        st['total_coin'] = round(st['total_coin'], 2) if st['total_coin'] else None
        statistics_data.append(st)
    # 加入统计列
    if len(statistics_data) > 0:
        statistics_data.append({
            'user_id': -1,
            'admin_name': '合计',
            'total_count': total_count,
            'level_count': ''.join(['%d%s' % (v, k) for k, v in Counter(total_levels).items() if k]),
            'total_coin': total_swiss_coin if total_swiss_coin else None
        })

    response_data = {
        'statistics': statistics_data,
        'records': records
    }
    return AllResponse.operate_successfully(data=response_data)


@work_lecture_api.get('/statisticsMonthly/')  # 按月统计数量
def lecture_statistics_monthly(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                               st: int = Query(1, ge=1, le=2)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    # 查询日期范围中的数据
    sql = """
            SELECT a.id,a.user_id,a.create_date,a.title,a.applicant,a.lecture_type,a.lecture_scale,a.effect_flag,
                a.annex,a.annex_url,a.level_flag,a.swiss_coin,b.admin_name  
            FROM work_lecture As a INNER JOIN ruizy_user As b ON a.user_id=b.id 
            WHERE a.is_active=1 AND a.create_date>=%s AND a.create_date<=%s AND a.applicant<>'营业部' 
            ORDER BY a.create_date DESC;
        """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[start_date, end_date])
    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if st == 2:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if st == 2 and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))

    month_list = datetime_utils.month_of_range(start=start_date, end=end_date, ft='%Y%m%d')
    # 对数据按人员按月份统计
    statistics_data, table_columns = work_utils.statistics_monthly(records, month_list=month_list)
    response_data = {
        'statistics_data': statistics_data,
        'table_columns': table_columns
    }
    return AllResponse.operate_successfully(data=response_data)

